﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Text;
using System.Data;
using Aspose.Cells;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace SUOASPX
{
    public class Common
    {
        /// <summary>
        /// 将报名信息表和照片进行合成的
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="imgPath"></param>
        /// <param name="outFileName"></param>
        public static bool Compose(string fileName, string imgPath, string outFileName)
        {
            HSSFWorkbook hssfworkbook;
            //打开模板文件到文件流中
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                //将文件流中模板加载到工作簿对象中
                hssfworkbook = new HSSFWorkbook(file);

                //HttpServerUtility _server1 = HttpContext.Current.Server;
                //string TemplateFiletop1 = _server1.MapPath(imgPath);
                if (File.Exists(imgPath))
                {
                    FileStream fs = new FileStream(imgPath, FileMode.Open, FileAccess.Read);
                    byte[] Content = new byte[Convert.ToInt32(fs.Length)];
                    fs.Read(Content, 0, Convert.ToInt32(fs.Length));
                    int pictureIdx = hssfworkbook.AddPicture(Content, NPOI.SS.UserModel.PictureType.JPEG);

                    HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("sheet1");
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
                    //##处理照片位置，【图片左上角为（6, 1）第2+1行6+1列，右下角为（9, 9）第6+1行8+1列】
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 4, 1, 6, 7);
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    //保存到指定位置
                    FileStream fileout = new FileStream(outFileName, FileMode.Create);
                    hssfworkbook.Write(fileout);
                    fileout.Close();
                    return true;
                }
                else
                    return false;
            }
        }
        /// <summary>
        /// 输出报名信息表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pcid"></param>
        /// <returns></returns>
        public static string ExportBMXX(DataTable dt, string Modellfile, ref string error)
        {
            try
            {
                //打开模版文件
                string path = System.Web.HttpContext.Current.Server.MapPath("./Files/" + Modellfile);
                Workbook workbook = new Workbook(path);
                //打开sheet
                Worksheet worksheet = workbook.Worksheets[0];//读取第一个工作表
                Cells cells = worksheet.Cells;//在Excel模班中填入信息
                cells[1, 1].PutValue(dt.Rows[0][1].ToString());
                cells[1, 3].PutValue(dt.Rows[0][5].ToString());
                cells[2, 1].PutValue(dt.Rows[0][6].ToString());
                cells[2, 3].PutValue(dt.Rows[0][7].ToString());
                cells[3, 1].PutValue(dt.Rows[0][9].ToString());
                cells[3, 3].PutValue(dt.Rows[0][8].ToString());
                cells[4, 1].PutValue(dt.Rows[0][0].ToString());
                cells[5, 1].PutValue(dt.Rows[0][2].ToString());
                cells[6, 1].PutValue(dt.Rows[0][3].ToString());
                string saveFileName = System.Web.HttpContext.Current.Server.MapPath("./Files/result/" + dt.Rows[0][0].ToString() + ".xls");
                if (File.Exists(saveFileName))
                {
                    File.Delete(saveFileName);//如果存在就删除
                }
                workbook.Save(saveFileName);//生成报名信息表，但没有合成照片
                string photoFileName = System.Web.HttpContext.Current.Server.MapPath(dt.Rows[0][10].ToString());
                string outFileName = System.Web.HttpContext.Current.Server.MapPath("./Files/result/" + dt.Rows[0][0].ToString() + ".xls");
                workbook = null;
                if (Compose(saveFileName, photoFileName, outFileName))
                {
                    error = "";
                    return "./Files/result/" + dt.Rows[0][0].ToString() + ".xls";
                }
                else
                {
                    error = "照片合成失败";
                    return "";
                }
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return "";
            }
        }

        /// <summary>
        /// 输出信息列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="pcid"></param>
        /// <returns></returns>
        public static bool ExportList(DataTable dt, string ModellFile, ref string error)
        {
            try
            {
                //打开模版文件
                string path = System.Web.HttpContext.Current.Server.MapPath("./files/" + ModellFile);
                Workbook workbook = new Workbook(path);
                //打开sheet
                Worksheet worksheet = workbook.Worksheets[0];
                Cells cells = worksheet.Cells;
                //加入样式1,上下左右居中，带四周边界的
                Aspose.Cells.Style style1 = new Aspose.Cells.Style();
                style1.HorizontalAlignment = TextAlignmentType.Left;
                style1.VerticalAlignment = TextAlignmentType.Center;
                style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                style1.Font.Size = 9;
                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    cells[i + 1, 0].PutValue(dr[0]);
                    cells[i + 1, 0].SetStyle(style1);
                    cells[i + 1, 1].PutValue(dr[1]);
                    cells[i + 1, 1].SetStyle(style1);
                    cells[i + 1, 2].PutValue(dr[2]);
                    cells[i + 1, 2].SetStyle(style1);
                    cells[i + 1, 3].PutValue(dr[3]);
                    cells[i + 1, 3].SetStyle(style1);
                    cells[i + 1, 4].PutValue(dr[4]);
                    cells[i + 1, 4].SetStyle(style1);
                    cells[i + 1, 5].PutValue(dr[5]);
                    cells[i + 1, 5].SetStyle(style1);
                    cells[i + 1, 6].PutValue(dr[6]);
                    cells[i + 1, 6].SetStyle(style1);
                    cells[i + 1, 7].PutValue(dr[7]);
                    cells[i + 1, 7].SetStyle(style1);
                    cells[i + 1, 8].PutValue(dr[8]);
                    cells[i + 1, 8].SetStyle(style1);
                    i++;
                }
                workbook.Save(HttpContext.Current.Response, ModellFile, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.Close();
                workbook = null;
                error = "";
                return true;
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return false;
            }
        }
    }
}